﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace Wild2.Models.Notifies
{
    public class Notifies
    {
        public static int GetCountNewNotifies(int UserID)
        {
            int tid = -1;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT COUNT(ID) as CNT FROM [Notifies] WHERE ([OwnerUserID] = @UID) and ([isRead] = 'FALSE')", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = UserID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    tid = Convert.ToInt32(reader["CNT"]);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            return tid;
        }

        public static int AddNotify(int? UserID, string nick, int CommentID, string text, int OwnerID, string EventType)
        {
            //EventType = answer, sandbox
            int tid = -1;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"INSERT INTO Notifies([Type],[KtoID], [KtoNick], [WhereID], [Text], [Date], [OwnerUserID], [IsRead])
							                      VALUES(@EventType, @UID, @Nick, @CID, @Text, GETDATE(), @BlogOwnerUID, 'TRUE');", conn);
                if (UserID.HasValue) cmd.Parameters.Add("@UID", SqlDbType.Int).Value = UserID;
                else cmd.Parameters.Add("@UID", SqlDbType.Int).Value = DBNull.Value;
                cmd.Parameters.Add("@Nick", SqlDbType.NVarChar, 20).Value = nick;
                cmd.Parameters.Add("@CID", SqlDbType.Int).Value = CommentID;
                cmd.Parameters.Add("@Text", SqlDbType.NVarChar, 4000).Value = text;
                cmd.Parameters.Add("@BlogOwnerUID", SqlDbType.Int).Value = OwnerID;
                cmd.Parameters.Add("@EventType", SqlDbType.NVarChar, 50).Value = EventType;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            return tid;
        }

        public static List<NotifyData> GetNotifies(int UserID)
        {
            List<NotifyData> notifies = new List<NotifyData>();
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT TOP(30) [ID], [Type], [KtoID], [KtoNick], [WhereID], [Text], [Date], [isRead] FROM [Notifies] WHERE ([OwnerUserID] = @UID) ORDER BY [isRead], [Date] DESC", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = UserID;
                cmd.Connection.Open();
                SqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    NotifyData notifyData = new NotifyData();
                    notifyData.ID = Convert.ToInt32(reader["ID"]);
                    notifyData.Type = reader["Type"].ToString();
                    if (reader["KtoID"] != DBNull.Value) notifyData.KtoID = Convert.ToInt32(reader["KtoID"]);
                    if (reader["KtoNick"] != DBNull.Value) notifyData.KtoNick = reader["KtoNick"].ToString();
                    if (reader["WhereID"] != DBNull.Value) notifyData.WhereID = Convert.ToInt32(reader["WhereID"]);
                    notifyData.Text = reader["Text"].ToString();
                    notifyData.Date = Convert.ToDateTime(reader["Date"]);
                    notifyData.isRead = Convert.ToBoolean(reader["isRead"]);
                    notifies.Add(notifyData);
                }
                reader.Close();
                cmd.Connection.Close();
            }
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnect"].ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"UPDATE [Notifies] SET [isRead] = 'TRUE' WHERE ([OwnerUserID] = @UID)", conn);
                cmd.Parameters.Add("@UID", SqlDbType.Int).Value = UserID;
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            return notifies;
        }
    }
}